package org.bean2sql.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.bean2sql.BeanSqlGenerator;
import org.bean2sql.generator.SqlCommand;

public class DemoDB {

    public static void main(String[] args) {
	Person person = new Person();
	person.setId(1);
	person.setName("John Harald");
	person.setEmail("harald@web.de");

	Person person2 = new Person();
	person2.setId(2);
	person2.setName("Albert Miller");
	person2.setEmail("miller@mills.com");

	List<Object> beanList = new ArrayList<Object>();
	beanList.add(person);
	beanList.add(person2);

	BeanSqlGenerator sqlGen = new BeanSqlGenerator();
	sqlGen.setDateFormat("yyyy/MM/dd");

	ResultSetMetaData rsmd = null;
	ResultSet rs = null;
	Statement stmt = null;
	Connection con = null;
	try {

	    con = connect();

	    boolean createOk = false;
	    int timeOut = 0;
	    String sql = sqlGen.getSql(SqlCommand.CREATE, person, "");
	    stmt = con.createStatement();
	    while (!createOk && timeOut < 3) {
		System.out.println("[CREATING the table] \n" + sql);
		try {
		    stmt.executeUpdate(sql);
		    createOk = true;
		} catch (Exception e) {
		    timeOut++;
		    String sqlDropTable = sqlGen.getSql(SqlCommand.DROP_TABLE,
			    person, "");
		    System.out.println("");
		    System.out
			    .println("[Table already exists and will be dropped dropped!]\n"
				    + sqlDropTable);
		    stmt.executeUpdate(sqlDropTable);
		    System.out.println("");
		}
		con.commit();
	    }
	    
	    sql = sqlGen.getSql(SqlCommand.INSERT, person, "");
	    System.out.println("[Single INSERT SQL output] \n" + sql);
	    stmt = con.createStatement();
	    stmt.execute(sql);

	    System.out.println("");

	    List<String> batch = sqlGen.getSql(SqlCommand.INSERT, beanList, "");
	    System.out.println("[Multiple INSERT SQL output]");
	    for (String sqlItem : batch) {
		System.out.println(sqlItem);
		stmt.execute(sqlItem);
	    }

	    System.out.println("");

	    // person.setBirth(DateFormat.getDateInstance().parse("10/05/1960"));
	    person.setEmail("update@bean2sql.org");
	    sql = sqlGen.getSql(SqlCommand.UPDATE, person, "");
	    System.out.println("[Single UPDATE SQL output]\n" + sql);
	    stmt.executeUpdate(sql);

	    System.out.println("");

	    sql = sqlGen.getSql(SqlCommand.DELETE, person, "");
	    System.out.println("[Single DELETE SQL output]\n" + sql);
	    stmt.execute(sql);

	    System.out.println("");

	    sql = "select * from Pessoa";
	    rs = stmt.executeQuery(sql);

	    System.out.println("[Listing the RECORDS]\n" + sql);
	    System.out
		    .println("-------------------------------------------------------");
	    // Get result set meta data
	    rsmd = rs.getMetaData();
	    int numColumns = rsmd.getColumnCount();

	    while (rs.next()) {
		String row = "";
		for (int i = 1; i < numColumns + 1; i++) {
		    row += rs.getString(i) + ", ";
		}
		System.out.println("ROW: " + row);
	    }

	    System.out.println("");

	} catch (Exception e) {
	    new Exception("Error!", e).printStackTrace();
	} finally {
	    System.out.println("[Finishing...]");
	    try {
		rsmd = null;
		rs.close();
		stmt.close();
		con.close();
	    } catch (SQLException e) {
		e.printStackTrace();
	    }

	}
    }

    private static Connection connect() {
	try {
	    System.out.print("[CONNECTING...");
	    Class.forName("org.hsqldb.jdbcDriver");
	    Connection con = DriverManager
		    .getConnection("jdbc:hsqldb:file:C:\\java\\eclipse\\workspace\\Bean2SQL\\db");
	    con.setAutoCommit(true);
	    System.out.println("OK]\n");
	    return con;
	} catch (ClassNotFoundException e) {
	    e.printStackTrace();
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	return null;
    }
}
